package com.example.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.example.easyexcel.entity.UserExcel;
import com.example.easyexcel.entity.UserInfo;
import com.example.easyexcel.handler.CustomCellWriteHandler;
import com.example.easyexcel.util.EasyExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.nio.file.Paths;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 使用模板生成excel
 *
 * @author tu228
 */
@Slf4j
@RestController
@RequestMapping("/complexExcel")
public class ComplexExcelController {

    /**
     * 实现自定义表头并在同一个sheet中分页
     *
     * @param response 响应对象
     */
    @GetMapping("/export")
    public void exportExcel(HttpServletResponse response){
        try {
            List<UserInfo> userInfos = buildUserInfos();
            EasyExcelUtils.download(response, userInfos);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * excel合并单元格
     *
     * @param response 响应对象
     */
    @GetMapping("/excelMerge")
    public void excelMerge(HttpServletResponse response){
        // 构造部门
        String dept[] = {"部门1", "部门2", "部门3", "bumen4"};
        // 工资范围
        int salaryRange[] = {5000, 10000, 15000};
        // 构造list
        List<UserExcel> excel = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            Instant instant = LocalDateTime.now().minusYears(Math.round(Math.random() * 20)).toInstant(ZoneOffset.ofHours(8));
            String randomDept = dept[(int) (Math.random() * 4)];
            String randomDeptDescription = randomDept + "的描述信息";
            Double salary = 5000 + BigDecimal.valueOf(Math.random() * 10000).setScale(2, BigDecimal.ROUND_UP).doubleValue();
            String salaryRangeString = "未知";
            for (int i1 = 0; i1 < salaryRange.length; i1++) {
                int end = salaryRange[i1];
                if (salary < end) {
                    salaryRangeString = salaryRange[i1 - 1] + "-" + end;
                    break;
                }
            }
            UserExcel user = UserExcel.builder().dept(randomDept).deptDescription(randomDeptDescription).id((long) i).name("tx-" + i).birthday(LocalDateTime.ofInstant(instant, ZoneId.systemDefault())).salary(salary).salaryRange(salaryRangeString).build();
            excel.add(user);
        }
        // 按照部门、工资范围排序
        excel.sort((one, two) -> {
            int i = one.getDept().compareTo(two.getDept());
            if (i == 0) {
                return one.getSalaryRange().compareTo(two.getSalaryRange());
            }
            return i;
        });
        // 文件保存路径
//        String baseDir = System.getProperty("user.dir");
        String baseDir = "E:\\office\\study\\tmp\\easyExcel\\file";
        String fileName = Paths.get(baseDir, "testExcel.xlsx").toString();
        EasyExcel.write(fileName, UserExcel.class).registerWriteHandler(new CustomCellWriteHandler(2, 2, new int[]{
                // 需要合并部门列、部门描述列、工资范围列
                0, 1, 2}, ((cur, pre) -> {
            // 部门名称相同 && 工资范围相同才需要合并
            String curDept = cur.getCell(0).getStringCellValue();
            String preDept = pre.getCell(0).getStringCellValue();
            String curSalaryRange = cur.getCell(2).getStringCellValue();
            String preSalaryRange = pre.getCell(2).getStringCellValue();
            return curDept.equals(preDept) && curSalaryRange.equals(preSalaryRange) ? true : false;
        }))).sheet("用户信息").doWrite(excel);
    }

    /**
     * 动态表头与合并单元格测试
     *
     * @param response 响应对象
     */
    @GetMapping("/dynamicHeadAndMerge")
    public void dynamicHeadAndMerge(HttpServletResponse response){
        try {
            // 获取动态表头
            List<List<String>> dynamicHeadList = getDynamicHeadList(6);

            // 构建表格主体数据
            List<List<Object>> dynamicUserDataList = getDynamicUserDataList(6);

            // 将数据写入excel
            // 文件名称
            String fileName = "动态表头与合并单元格测试";
            // sheet名称
            String sheetName = "测试sheet";
            List<CustomCellWriteHandler> customCellWriteHandlerList = EasyExcelUtils.getCustomCellWriteHandlerList();
            // 表格样式
            HorizontalCellStyleStrategy headStyleStrategy = EasyExcelUtils.headStyleStrategy();
            // 列宽样式
            AbstractColumnWidthStyleStrategy columnWidthStyleStrategy = EasyExcelUtils.getColumnWidthStyleStrategy();
            // 行高样式
            SimpleRowHeightStyleStrategy rowHeightStyleStrategy = EasyExcelUtils.getRowHeightStyleStrategy();
            // 动态表头与合并单元格测试表格生成
            EasyExcelUtils.dynamicHeadAndMergeExcel(response, fileName, sheetName, customCellWriteHandlerList,
                    headStyleStrategy, columnWidthStyleStrategy, rowHeightStyleStrategy,
                    dynamicHeadList, dynamicUserDataList);
        } catch (Exception e) {
            log.error("#dynamicHeadAndMerge# 动态表头与合并单元格测试异常", e);
        }
    }

    /**
     * 获取动态表头
     *
     * @param workHisNum 工作经历数
     */
    private List<List<String>> getDynamicHeadList(int workHisNum) {
        List<List<String>> dynamicHeadList = Lists.newArrayList();
        List<String> innerHeadList = Lists.newArrayList();
        innerHeadList.add("姓名");
        innerHeadList.add("性别编码");
        innerHeadList.add("性别描述");
        innerHeadList.add("爱好");
        innerHeadList.add("年龄");
        innerHeadList.add("身份证号");
        innerHeadList.add("地址");
        innerHeadList.add("当前工作单位");
        innerHeadList.add("当前公司名称");
        innerHeadList.add("当前工号");
        innerHeadList.add("当前公司地址");
        for (int i = 1; i <= workHisNum; i++) {
            innerHeadList.add("工作经历" + i);
        }

        innerHeadList.forEach(title -> dynamicHeadList.add(Arrays.asList(title, title)));
        log.info("#getDynamicHeadList# 请求参数-workHisNum:{},返回结果-dynamicHeadList:{}",workHisNum,dynamicHeadList);
        return dynamicHeadList;
    }

    /**
     * 构建数据
     */
    private List<UserInfo> buildUserInfos() {
        List<UserInfo> userInfos = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            UserInfo userInfo = new UserInfo();
            userInfo.setName("姓名" + i);
            userInfo.setSex("男");
            userInfo.setAge(17 + i);
            userInfo.setCardNo("身份证号" + i);
            userInfo.setAddress("住址" + i);
            userInfo.setCompanyName("公司名称" + i);
            userInfo.setWorkNo("工号" + i);
            userInfo.setCompanyAddress("公司地址" + i);
            userInfos.add(userInfo);
        }
        return userInfos;
    }

    /**
     * 构建动态用户数据
     *
     * @param workHisNum 工作经历数
     */
    private List<List<Object>> getDynamicUserDataList(int workHisNum) {
        List<List<Object>> excelDataList = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            List<Object> oneRowDataList = Lists.newArrayList();
            oneRowDataList.add("姓名" + i);
            if (i < 5 || (10 <= i && i < 15)){
                oneRowDataList.add("M");
                oneRowDataList.add("男");
                oneRowDataList.add("爱好女");
            }else {
                oneRowDataList.add("F");
                oneRowDataList.add("女");
                oneRowDataList.add("爱好男");
            }
            oneRowDataList.add(17 + i);
            oneRowDataList.add("身份证号" + i);
            oneRowDataList.add("住址" + i);
            oneRowDataList.add("工作单位" + i);
            oneRowDataList.add("公司名称" + i);
            oneRowDataList.add("工号" + i);
            oneRowDataList.add("公司地址" + i);
            for (int j = 1; j <= workHisNum; j++) {
                oneRowDataList.add("我的工作" + j);
            }
            excelDataList.add(oneRowDataList);
        }

        log.info("#getDynamicUserDataList# 请求参数-workHisNum:{},返回结果-excelDataList:{}",workHisNum,excelDataList);
        return excelDataList;
    }

}
